/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.essilab.analyzer.analyzer.db;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.essilab.analyzer.Configuration.FileLoader;
import org.essilab.analyzer.analyzer.Term;

/**
 * This class is used to save or retrieve a term and his relations.
 *
 * @author Mohamed-Amine Chouchene, Ahcène Idinarere, Vincent Nock, Alain
 * Janin-Manificat
 */
public class ServiceDAO {

    Connection connection;
    private String user = "";
    private String password = "";
    PreparedStatement insertTerm = null;
    PreparedStatement insertTypeRelation = null;
    PreparedStatement insertTraduction = null;
    PreparedStatement insertTrenslation = null;
    PreparedStatement selectType = null;
    PreparedStatement selectTrenslation = null;
    PreparedStatement selectTerm = null;
    PreparedStatement selectIdsTypes = null;
    PreparedStatement selectTermById = null;
    PreparedStatement selectTypeById = null;
    PreparedStatement insertTermRelation = null;

    public ServiceDAO() {
    }

    /**
     * Init the BDD connection.
     *
     * @param user String - DB user name.
     * @param password String - DB corresponding password.
     */
    public ServiceDAO(String user, String password) {
        this.user = user;
        this.password = password;
        if (connect()) {
            try {
                init();
            } catch (SQLException e) {
            }
        }
    }

    /**
     * Connection to MySql DB localhost Mysql using base : wiktionnary
     *
     * @return boolean - True if the connection is established, false otherwise.
     */
    public final boolean connect() {
        try {
            System.out.println("Loading driver...");
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded!");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/wiktionnary", user, password);
            System.out.println("Prepare Statement...");
            insertTerm = connection.prepareStatement("INSERT INTO `terms`(`title`, `date`, `language`) VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS);
            insertTypeRelation = connection.prepareStatement("INSERT INTO `terms_has_types`(`types_idtypes`, `terms_idterms`) VALUES (?,?)");
            insertTraduction = connection.prepareStatement("INSERT INTO `means`(`traduction_idtraduction`, `terms_idterms`) VALUES (?,?)");
            insertTrenslation = connection.prepareStatement("INSERT INTO `translation`(`title`, `lang`) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
            selectType = connection.prepareStatement("SELECT idtypes FROM `wiktionnary`.`types` where `title` = ?");
            selectTrenslation = connection.prepareStatement("SELECT `idtraduction` FROM `translation` WHERE `title` = ?");
            selectTerm = connection.prepareStatement("SELECT  `idterms` FROM  `terms` WHERE  `title` = ?");
            selectIdsTypes = connection.prepareStatement("SELECT `types_idtypes` FROM `terms_has_types` WHERE `terms_idterms` = ?");
            selectTermById = connection.prepareStatement("SELECT `title`  FROM `terms` WHERE `idterms` = ?");
            selectTypeById = connection.prepareStatement("SELECT  `title` FROM `types` WHERE `idtypes` = ?");
            insertTermRelation = connection.prepareStatement("SELECT `terms_idterms1` FROM ? WHERE terms_idterms = ?");
            System.out.println("End Prepare Statement...");
            return true;
        } catch (Exception ex) {
            Logger.getLogger(ServiceDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /**
     * Fill the "types" table from property file "types.txt".
     *
     * @throws SQLException
     */
    private void init() throws SQLException {
        FileLoader fl = new FileLoader("types.txt");
        List<String> types = fl.getAllValues();
        try (PreparedStatement ps = connection.prepareStatement("INSERT INTO `wiktionnary`.`types` (`title`) " + "VALUES (?)")) {
            for (String type : types) {
                if (-1 == checkTypeExistence(type)) {
                    ps.setString(1, type);
                    ps.execute();
                }
            }
        }
    }

    /**
     * Insert a Term in the "terms" table. Then insert the term type in
     * "terms_has_types". If the Term has translations, add them in the table
     * "translation" and add the relations in "means".
     *
     * @param term Term - The Term to save in the DB.
     * @throws SQLException
     */
    public void insertTerm(Term term) throws SQLException {
        int idtype;
        int idterm;
        int idtranslation;
        String date = term.getCreatedAt().split("T")[0];
        insertTerm.setString(1, term.getTitle());
        insertTerm.setDate(2, Date.valueOf(date));
        insertTerm.setString(3, term.getLang());
        insertTerm.executeUpdate();
        ResultSet rs = insertTerm.getGeneratedKeys();
        if (rs.next()) {
            // Retrieve the auto generated key(s).
            idterm = rs.getInt(1);

            // Check if each type exist and isert it into type_has terms joint table
            for (String type : term.getTypes()) {
                idtype = checkTypeExistence(type);
                if (-1 != idtype) {
                    // Insert into terms_has_types joint table
                    insertTypeRelation.setInt(1, idtype);
                    insertTypeRelation.setInt(2, idterm);
                    try {
                        insertTypeRelation.execute();
                    } catch (Exception e) {
                        //e.printStackTrace();
                    }
                }
            }

            // Insert term translations and translation relations.
            Map<String, List<String>> translations = term.getTranslations();
            for (String lang : translations.keySet()) {
                for (String translation : translations.get(lang)) {
                    idtranslation = getOrCreateTranslation(translation, lang);
                    if (-1 != idtranslation) {
                        // Insert term types
                        insertTraduction.setInt(1, idtranslation);
                        insertTraduction.setInt(2, idterm);
                        try {
                            insertTraduction.execute();
                        } catch (Exception e) {
                            // e.printStackTrace();
                        }
                    }
                }
            }
        }
        rs.close();
    }

    /**
     * Return the id in table "types" of the given type title.
     *
     * @param type String - the type title to look for
     * @return int - The type id if exists, return -1 otherwise.
     * @throws SQLException
     */
    public int checkTypeExistence(String type) throws SQLException {
        selectType.setString(1, type);
        selectType.setMaxRows(1);
        ResultSet rs = selectType.executeQuery();
        int value = -1;
        if (rs.next()) {
            value = rs.getInt(1);
        }
        rs.close();
        return value;
    }

    /**
     * Get the id of the given term translation in the given language. If the
     * translation doesn't exist yet, create it in table "translation".
     *
     * @param termTranslation String - the translation to look for.
     * @param lang
     * @return
     * @throws SQLException
     */
    public int getOrCreateTranslation(String termTranslation, String lang) throws SQLException { // Crrer autant d'id translation que de traduction ds chaque langue
        selectTrenslation.setString(1, termTranslation);
        selectTrenslation.setMaxRows(1);
        ResultSet rs = selectTrenslation.executeQuery();
        int value = -1;
        if (rs.next()) {
            value = rs.getInt(1);
        } else {
            insertTrenslation.setString(1, termTranslation);
            insertTrenslation.setString(2, lang);
            insertTrenslation.executeUpdate();
            ResultSet rs2 = insertTrenslation.getGeneratedKeys();
            if (rs2.next()) {
                // Retrieve the auto generated key(s).
                value = rs2.getInt(1);
            }
            rs2.close();
        }
        rs.close();
        return value;
    }

    /**
     * Insert the relations of the given Term in the appropriate tables.
     *
     * @param term Term - The relations of this term will be stored in the DB.
     * @throws SQLException
     */
    public void insertTermRelations(Term term) throws SQLException {
        int idterm;
        int idterm1;

        if (0 != term.getAbbreviations().size()) {
            for (String relation : term.getAbbreviations()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "abbreviations");
                }
            }
        }

        if (0 != term.getAlmostSynonymous().size()) {
            for (String relation : term.getAlmostSynonymous()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "almost_synonymous");
                }
            }
        }

        if (0 != term.getAntonym().size()) {
            for (String relation : term.getAntonym()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "antonyms");
                }
            }
        }

        if (0 != term.getDerives().size()) {
            for (String relation : term.getDerives()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "derives");
                }
            }
        }

        if (0 != term.getDimintives().size()) {
            for (String relation : term.getDimintives()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "dimintives");
                }
            }
        }

        if (0 != term.getError().size()) {
            for (String relation : term.getError()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "errors");
                }
            }
        }

        if (0 != term.getHolonyms().size()) {
            for (String relation : term.getHolonyms()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "holonyms");
                }
            }
        }

        if (0 != term.getHyperonyms().size()) {
            for (String relation : term.getHyperonyms()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                }
            }
        }

        if (0 != term.getHyponyms().size()) {
            for (String relation : term.getHyponyms()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "hyponyms");
                }
            }
        }

        if (0 != term.getMeronymes().size()) {
            for (String relation : term.getMeronymes()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "meronyms");
                }
            }
        }

        if (0 != term.getRelated().size()) {
            for (String relation : term.getRelated()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "related");
                }
            }
        }

        if (0 != term.getSynonymous().size()) {
            for (String relation : term.getSynonymous()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "synonymous");
                }
            }
        }

        if (0 != term.getTroponyms().size()) {
            for (String relation : term.getTroponyms()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "troponyms");
                }
            }
        }

        if (0 != term.getTypographicalVariants().size()) {
            for (String relation : term.getTypographicalVariants()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "typographical_variants");
                }
            }
        }

        if (0 != term.getVariantOrthoraphic().size()) {
            for (String relation : term.getVariantOrthoraphic()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "variant_orthoraphic");
                }
            }
        }

        if (0 != term.getVariants().size()) {
            for (String relation : term.getVariants()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "variants");
                }
            }
        }

        if (0 != term.getVocabulary().size()) {
            for (String relation : term.getVocabulary()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "vocabulary");
                }
            }
        }

        if (0 != term.getfAdj().size()) {
            for (String relation : term.getfAdj()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_adjectif");
                }
            }
        }

        if (0 != term.getfAdjIndef().size()) {
            for (String relation : term.getfAdjIndef()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_adjectif_undefine");
                }
            }
        }

        if (0 != term.getfInterj().size()) {
            for (String relation : term.getfInterj()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_interjectif");
                }
            }
        }

        if (0 != term.getfLAdv().size()) {
            for (String relation : term.getfLAdv()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_locution_verb");
                }
            }
        }

        if (0 != term.getfLNoun().size()) {
            for (String relation : term.getfLNoun()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form__locution_noun");
                }
            }
        }

        if (0 != term.getfLVerb().size()) {
            for (String relation : term.getfLVerb()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_locution_verb");
                }
            }
        }

        if (0 != term.getfNoun().size()) {
            for (String relation : term.getfNoun()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_noun");
                }
            }
        }

        if (0 != term.getfVerb().size()) {
            for (String relation : term.getfVerb()) {
                if (-1 != (idterm = getTermId(term.getTitle())) && -1 != (idterm1 = getTermId(relation))) {
                    insertTermRelation(idterm, idterm1, "form_verb");
                }
            }
        }
    }

    /**
     * Return the id in table "idterms" of the given term identified by his
     * title.
     *
     * @param type String - the term title to look for
     * @return int - The term id if exists, return -1 otherwise.
     * @throws SQLException
     */
    public int getTermId(String title) {
        int value = -1;
        try {
            selectTerm.setString(1, title);
            selectTerm.setMaxRows(1);
            ResultSet rs = selectTerm.executeQuery();
            if (rs.next()) {
                value = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(ServiceDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return value;
    }

    /**
     * Insert a relation between two terms in the given table.
     *
     * @param idterm int - id of the first term.
     * @param idterm1 int - id of the second term.
     * @param tableName String - name of the table where the relation will be
     * stored.
     */
    private void insertTermRelation(int idterm, int idterm1, String tableName) {
        try {
            PreparedStatement ps = connection.prepareStatement("INSERT INTO `" + tableName + "` (`terms_idterms`, `terms_idterms1`) VALUES (?,?)");
            ps.setInt(1, idterm);
            ps.setInt(2, idterm1);
            ps.execute();
            ps.close();
        } catch (SQLException ex) {
            //ex.printStackTrace();
        }
    }

    /**
     * Get all the types associated with a term.
     *
     * @param term String- Return the types of this term.
     * @return List<String> - the list of types of this term.
     */
    public List<String> getTermType(String term) {
        List<String> type = new ArrayList<String>();

        int termID = getTermId(term);

        if (-1 != termID) {
            List<Integer> idtypes = getIdTypeOfTerm(termID);

            for (int idtype : idtypes) {
                type.add(getType(idtype));
            }
        }

        return type;
    }

    /**
     * Return the type label identified with given id in table "types".
     *
     * @param id int - id of the type.
     * @return String - type label identified by the given id.
     */
    private String getType(int id) {
        String type = null;
        try {
            selectTypeById.setInt(1, id);
            selectTypeById.setMaxRows(1);
            ResultSet rs = selectTypeById.executeQuery();
            if (rs.next()) {
                type = rs.getString(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(ServiceDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return type;

    }

    /**
     * Get all the type id associated with the term identified by given id.
     *
     * @param idterm int - Id of the term used to retrieve the associated type
     * id.
     * @return List<Integer> - The id list of the term types.
     */
    public List<Integer> getIdTypeOfTerm(int idterm) {
        List<Integer> idtypes = new ArrayList<Integer>();
        try {
            selectIdsTypes.setInt(1, idterm);
            ResultSet rs = selectIdsTypes.executeQuery();
            while (rs.next()) {
                idtypes.add(rs.getInt(1));
            }
            rs.close();
        } catch (SQLException ex) {
        }

        return idtypes;
    }

    
    
    
    
   /*******************************************************************
    * The three methods above are not used now. They are made to *
    * retrieve the list a relations of a Term from the DB.            *
    *******************************************************************/


    public Map<String, String> getTermRelations(Term term) {
        //Renvoyer la liste des relations avec le type pour chaque relation : mot - type
        Map<String, String> relations = new HashMap<String, String>();
        int idTerm;
        String relation = null;
        idTerm = getTermId(term.getTitle());
        if (-1 != idTerm) {
            if (null != (relation = getTermRelation(idTerm, "abbreviations"))) {
                relations.put(relation, "abbreviations");
            }
        }

        return relations;
    }

    public String getTermRelation(int idTerm, String tableName) {
        String relation = null;
        int idRelation = -1;
        try {
            //insertTermRelation = connection.prepareStatement("SELECT `terms_idterms1` FROM `" + tableName + "` WHERE terms_idterms = ?");
            insertTermRelation.setString(1, tableName);
            insertTermRelation.setInt(2, idTerm);
            ResultSet rs = insertTermRelation.executeQuery();
            while (rs.next()) {
                idRelation = rs.getInt(1);
            }
            if (-1 != idRelation) {
                relation = getTermById(idTerm);
            }
            rs.close();
            //insertTermRelation.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return relation;
    }

    private String getTermById(int idterm) {
        String term = null;
        try {
            selectTermById.setInt(1, idterm);
            ResultSet rs = selectTermById.executeQuery();
            while (rs.next()) {
                term = rs.getString(1);
            }
            rs.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return term;
    }
}
